********************************************************************************
************************* Do File (7): Table A1.1*******************************
********************************************************************************

clear
set more off

use "$data/EPH_1015_format.dta", clear

destring occupation occupation_unemp occup_all, replace

*** Keep only observations that have all controls used

keep if (occupation !=. & underemp != . & cont_pens !=. & domwk_all !=. & linc_mjob_base08 !=. & lwagehr_mjob_base08 !=. & linc_total_base08 != . & hours_mjob !=. & age !=. & hhsize !=. & tenure !=. & msa !=. & educyr !=. ///
& attsch_ever !=. & native !=. & lit !=. & gender !=.) | (occupation_unemp != . & age !=. & hhsize !=. & msa !=. & educyr !=. & attsch_ever !=. & native !=. & lit !=. & gender !=.)

********************************************************************************
********************************************************************************

* Generate lags of pension contribution and domestic worker status

sort CODUSU nro_hogar pid quarter year

gen lcont_pens = cont_pens[_n-1] if CODUSU == CODUSU[_n-1] & nro_hogar == nro_hogar[_n-1] & pid == pid[_n-1] & quarter == quarter[_n-1]
label var lcont_pens "Made social security contribution last year"

gen ldomwk = domwk[_n-1] if CODUSU == CODUSU[_n-1] & nro_hogar == nro_hogar[_n-1] & pid == pid[_n-1] & quarter == quarter[_n-1]
label var ldomwk "Was domestic worker last year"

//gen l${ctrl_group} = ${ctrl_group}[_n-1] if CODUSU == CODUSU[_n-1] & nro_hogar == nro_hogar[_n-1] & pid == pid[_n-1] & quarter == quarter[_n-1]
//label var l${ctrl_group} "Was worker in ${ctrl_group} last year"

* Matrix for non-domestic workers

/*qui sum cont_pens if lcont_pens == 0 & domwk == 0 & ${ctrl_group} == 1 & gender == 0 & year == 2010
matrix define meanunreg = r(mean)

qui sum cont_pens if lcont_pens == 1 & domwk == 0 & ${ctrl_group} == 1 & gender == 0 & year == 2010
matrix define meanreg = r(mean)

qui sum cont_pens if lcont_pens != . & domwk == 0 & ${ctrl_group} == 1 & gender == 0 & year == 2010
matrix define meanall = r(mean)
*/

matrix define transitions = J(7,4,.)

* Domestic workers who were not registered in the previous year

qui sum cont_pens if lcont_pens == 0 & domwk == 1 & gender == 0 & year == 2011
matrix transitions[1,1] = round(r(mean),.001)

qui sum cont_pens if lcont_pens == 0 & domwk == 1 & gender == 0 & year == 2012
matrix transitions[2,1] = round(r(mean),.001)

qui sum cont_pens if lcont_pens == 0 & domwk == 1 & gender == 0 & inlist(year,2011,2012)
matrix transitions[3,1] = round(r(mean),.001)

qui sum cont_pens if lcont_pens == 0 & domwk == 1 & gender == 0 & year == 2013
matrix transitions[4,1] = round(r(mean),.001)

qui sum cont_pens if lcont_pens == 0 & domwk == 1 & gender == 0 & year == 2014
matrix transitions[5,1] = round(r(mean),.001)

qui sum cont_pens if lcont_pens == 0 & domwk == 1 & gender == 0 & year == 2015
matrix transitions[6,1] = round(r(mean),.001)

qui sum cont_pens if lcont_pens == 0 & domwk == 1 & gender == 0 & inlist(year,2013,2014,2015)
matrix transitions[7,1] = round(r(mean),.001)


* Low-wage female workers who were not registered in the previous year

qui sum cont_pens if lcont_pens == 0 & domwk == 0 & ${ctrl_group} == 1 & gender == 0 & year == 2011
matrix transitions[1,2] = round(r(mean),.001)

qui sum cont_pens if lcont_pens == 0 & domwk == 0 & ${ctrl_group} == 1 & gender == 0 & year == 2012
matrix transitions[2,2] = round(r(mean),.001)

qui sum cont_pens if lcont_pens == 0 & domwk == 0 & ${ctrl_group} == 1 & gender == 0 & inlist(year,2011,2012)
matrix transitions[3,2] = round(r(mean),.001)

qui sum cont_pens if lcont_pens == 0 & domwk == 0 & ${ctrl_group} == 1 & gender == 0 & year == 2013
matrix transitions[4,2] = round(r(mean),.001)

qui sum cont_pens if lcont_pens == 0 & domwk == 0 & ${ctrl_group} == 1 & gender == 0 & year == 2014
matrix transitions[5,2] = round(r(mean),.001)

qui sum cont_pens if lcont_pens == 0 & domwk == 0 & ${ctrl_group} == 1 & gender == 0 & year == 2015
matrix transitions[6,2] = round(r(mean),.001)

qui sum cont_pens if lcont_pens == 0 & domwk == 0 & ${ctrl_group} == 1 & gender == 0 & inlist(year,2013,2014,2015)
matrix transitions[7,2] = round(r(mean),.001)


* Domestic workers who were registered in the previous year

qui sum cont_pens if lcont_pens == 1 & domwk == 1 & gender == 0 & year == 2011
matrix transitions[1,3] = round(r(mean),.001)

qui sum cont_pens if lcont_pens == 1 & domwk == 1 & gender == 0 & year == 2012
matrix transitions[2,3] = round(r(mean),.001)

qui sum cont_pens if lcont_pens == 1 & domwk == 1 & gender == 0 & inlist(year,2011,2012)
matrix transitions[3,3] = round(r(mean),.001)

qui sum cont_pens if lcont_pens == 1 & domwk == 1 & gender == 0 & year == 2013
matrix transitions[4,3] = round(r(mean),.001)

qui sum cont_pens if lcont_pens == 1 & domwk == 1 & gender == 0 & year == 2014
matrix transitions[5,3] = round(r(mean),.001)

qui sum cont_pens if lcont_pens == 1 & domwk == 1 & gender == 0 & year == 2015
matrix transitions[6,3] = round(r(mean),.001)

qui sum cont_pens if lcont_pens == 1 & domwk == 1 & gender == 0 & inlist(year,2013,2014,2015)
matrix transitions[7,3] = round(r(mean),.001)


* Low-wage female workers who were registered in the previous year

qui sum cont_pens if lcont_pens == 1 & domwk == 0 & ${ctrl_group} == 1 & gender == 0 & year == 2011
matrix transitions[1,4] = round(r(mean),.001)

qui sum cont_pens if lcont_pens == 1 & domwk == 0 & ${ctrl_group} == 1 & gender == 0 & year == 2012
matrix transitions[2,4] = round(r(mean),.001)

qui sum cont_pens if lcont_pens == 1 & domwk == 0 & ${ctrl_group} == 1 & gender == 0 & inlist(year,2011,2012)
matrix transitions[3,4] = round(r(mean),.001)

qui sum cont_pens if lcont_pens == 1 & domwk == 0 & ${ctrl_group} == 1 & gender == 0 & year == 2013
matrix transitions[4,4] = round(r(mean),.001)

qui sum cont_pens if lcont_pens == 1 & domwk == 0 & ${ctrl_group} == 1 & gender == 0 & year == 2014
matrix transitions[5,4] = round(r(mean),.001)

qui sum cont_pens if lcont_pens == 1 & domwk == 0 & ${ctrl_group} == 1 & gender == 0 & year == 2015
matrix transitions[6,4] = round(r(mean),.001)

qui sum cont_pens if lcont_pens == 1 & domwk == 0 & ${ctrl_group} == 1 & gender == 0 & inlist(year,2013,2014,2015)
matrix transitions[7,4] = round(r(mean),.001)

* Write the names of rows and columns of the matrix

matrix rownames transitions = 2011 2012 "Average" 2013 2014 2015 "Average"
//matrix colnames transitions = "Domestic workers" "Other workers" "Domestic workers" "Other workers"

* Export the matrix to Excel

putexcel set "$tables/Table A1_1", replace

putexcel C1:D1, overwritefmt merge
putexcel C1 = "Not registered the previous year", hcenter border(bottom, thin)
putexcel (C1:D1), border(bottom, thin)

putexcel E1:F1, overwritefmt merge
putexcel E1 = "Registered the previous year", hcenter border(bottom, thin)
putexcel (E1:F1), border(bottom, thin)

putexcel A2 = "Period"
putexcel B2 = "Year"
putexcel C2 = "Domestic workers"
putexcel D2 = "Other workers"
putexcel E2 = "Domestic workers"
putexcel F2 = "Other workers"

putexcel (A2:F2), border(bottom, medium)

putexcel A3:A5, overwritefmt merge
putexcel A3 = "Pre-reform", vcenter

putexcel A6:A9, overwritefmt merge
putexcel A6 = "Post-reform", vcenter

putexcel B3 = matrix(transitions), rownames overwritefmt nformat(#.000)

putexcel(A5:F5), border(bottom, thin)
putexcel(A9:F9), border(bottom, medium)

putexcel save